﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HPSF;
using NPOI.HSSF.Record;
using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.Util;
using NPOI.Util.Collections;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Security.Claims;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace OkTool.Excel
{
    /// <summary>
    /// https://zyqok.blog.csdn.net/article/details/121994504
    /// </summary>
    public class ExcelUtil
    {
        private static String XLSX = ".xlsx";
        private static String XLS = ".xls";
        public static String ROW_MERGE = "row_merge";
        public static String COLUMN_MERGE = "column_merge";
        private static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
        private static String ROW_NUM = "rowNum";
        private static String ROW_DATA = "rowData";
        private static String ROW_TIPS = "rowTips";
        private static int CELL_OTHER = 0;
        private static int CELL_ROW_MERGE = 1;
        private static int CELL_COLUMN_MERGE = 2;
        private static int IMG_HEIGHT = 30;
        private static int IMG_WIDTH = 30;
        private static char LEAN_LINE = '/';
        private static int BYTES_DEFAULT_LENGTH = 10240;
        //public static  List<T> readFile<T>(FileInfo file) 
        //{
        //    string jsonText = File.ReadAllText(filePath);
        //    var array = JsonConvert.DeserializeObject<List<T>>(jsonText);
        //    return array;
        //}
        //public static JArray ReadFile(FileInfo file)
        //{
        //    return ReadExcel(null, file);
        //}

        //public static JArray ReadExcel(IFormFile value, FileInfo file)
        //{
        //    HttpContext.Current.Reques
        //    throw new NotImplementedException();
        //}
        //public static JArray ReadExcel(HttpPostedFile value, FileInfo file)
        //{
        //    throw new NotImplementedException();
        //}

        public static List<T> ReadFile<T>(FileInfo file)
        {
            JArray array = ReadFile(file);
            return GetBeanList<T>(array);

        }

        public static List<T> ReadMultipartFile<T>(MultipartFile mFile)
        {
            JArray array = ReadMultipartFile(mFile);
            return GetBeanList<T>(array);
        }


        public static JArray ReadFile(FileInfo file)
        {
            return ReadExcel(null, file);
        }
        public static JArray ReadMultipartFile(MultipartFile mfile)
        {
            return ReadExcel(mfile, null);
        }
        public static Dictionary<String, JArray> ReadFileManySheet(FileInfo file)
        {
            return ReadExcelManySheet(null, file);
        }

        public static Dictionary<String, JArray> ReadFileManySheet(MultipartFile file)
        {
            return ReadExcelManySheet(file, null);
        }
        private static Dictionary<string, JArray> ReadExcelManySheet(MultipartFile mFile, FileInfo file)
        {
            IWorkbook book = GetWordkBook(mFile, file);
            if (book == null)
            {
                return new Dictionary<string, JArray>();
            }
            Dictionary<string, JArray> map = new Dictionary<string, JArray>();
            for (int i = 0; i < book.NumberOfSheets; i++)
            {
                ISheet sheet = book.GetSheetAt(i);
                JArray arr = ReadSheet(sheet);
                map.Add(sheet.SheetName, arr);
            }
            book.Close();
            return map;
        }
        private static JArray ReadExcel(MultipartFile mfile, FileInfo file)
        {
            IWorkbook book = GetWordkBook(mfile, file);
            if (book == null)
            {
                return new JArray();
            }
            JArray array = ReadSheet(book.GetSheetAt(0));
            book.Close();
            return array;
        }

        private static JArray ReadSheet(ISheet sheet)
        {

            //首行下标
            int rowStart = sheet.FirstRowNum;
            //尾行下标
            int rowEnd = sheet.LastRowNum;
            //获取表头行
            IRow headRow = sheet.GetRow(rowStart);
            if (headRow == null)
            {
                return new JArray();
            }

            int cellStart = headRow.FirstCellNum;
            int cellEnd = headRow.LastCellNum;

            Dictionary<int, string> keyMap = new Dictionary<int, string>();
            for (int j = cellStart; j < cellEnd; j++)
            {
                //获取表头数据
                string val = GetCellValue(headRow.GetCell(j));
                if (!string.IsNullOrWhiteSpace(val))
                {
                    keyMap[j] = val;
                }
            }
            //如果表头没有数据则不进行解析
            if (keyMap.Count == 0)
            {
                return new JArray();
            }


            // 获取每行JSON对象的值
            JArray array = new JArray();
            // 如果首行与尾行相同，表明只有一行，返回表头数据
            if (rowStart == rowEnd)
            {
                JObject obj = new JObject();
                //添加行号
                obj.Add(ROW_NUM, 1);
                foreach (var key in keyMap.Keys)
                {
                    obj.Add(keyMap[key], "");
                }
                array.Add(obj);
                return array;
            }

            for (int i = rowStart + 1; i <= rowEnd; i++)
            {
                IRow eachRow = sheet.GetRow(i);

                JObject obj = new JObject();
                //添加行号
                obj.Add(ROW_NUM, i + 1);
                StringBuilder sb = new StringBuilder();
                for (int k = cellStart; k < cellEnd; k++)
                {
                    string val = GetCellValue(eachRow.GetCell(k));
                    //// 所有数据添加到里面，用于判断该行是否为空
                    sb.Append(val);
                    obj.Add(keyMap[k], val);
                }

                if (sb.Length > 0)
                {
                    array.Add(obj);
                }
            }

            return array;
        }
        private static string GetCellValue(ICell cell)
        {
            if (cell == null || cell.CellType == CellType.Blank)
            {
                return "";
            }

            if (cell.CellType == CellType.String)
            {
                string val = cell.StringCellValue;
                if (string.IsNullOrEmpty(val.Trim()))
                {
                    return "";
                }
                return val.Trim();
            }

            if (cell.CellType == CellType.Numeric)
            {
                string s = cell.NumericCellValue.ToString();
                if (Regex.IsMatch(s, @".*\.0*"))
                {
                    return s.Split('.')[0];
                }
                else
                {
                    return s;
                }
            }

            if (cell.CellType == CellType.Boolean)
            {
                return cell.BooleanCellValue.ToString();
            }

            return cell.CellFormula;
        }
        private static IWorkbook GetWordkBook(MultipartFile mfile, FileInfo file)
        {
            bool fileNotExit = (file == null || !file.Exists);
            if (mfile == null && fileNotExit)
            {
                return null;
            }

            string fileName;
            Stream stream;
            if (mfile != null)
            {
                stream = mfile.Stream;
                fileName = mfile.FileName.ToLower();
            }
            else
            {
                //本地文件
                stream = new FileStream(file.FullName, FileMode.Open, FileAccess.Read);
                fileName = file.FullName.ToLower();
            }
            IWorkbook book;
            if (fileName.EndsWith(XLSX))
            {
                book = new XSSFWorkbook(stream);
            }
            else if (fileName.EndsWith(XLS))
            {
                POIFSFileSystem fs = new POIFSFileSystem(stream);
                book = new HSSFWorkbook(fs);
            }
            else
            {
                return null;
            }
            stream.Close();
            return book;
        }
        private static List<T> GetBeanList<T>(JArray array)
        {
            List<T> list = new List<T>();
            Dictionary<int, string> uniqueMap = new Dictionary<int, string>();
            foreach (var item in array)
            {
                list.Add(GetBean<T>(item as JObject, uniqueMap));

            }

            return list;
        }
        private static T GetBean<T>(JObject obj, Dictionary<int, string> uniqueMap)
        {
            T t = (T)Activator.CreateInstance(typeof(T));
            PropertyInfo[] fields = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
            List<string> errMsgList = new List<string>();
            bool hasRowTipsField = false;
            System.Text.StringBuilder uniqueBuilder = new System.Text.StringBuilder();
            int rowNum = 0;

            foreach (PropertyInfo field in fields)
            {
                // 行号
                if (field.Name.ToLower() == ROW_NUM.ToLower())
                {
                    rowNum = obj.Value<int>(ROW_NUM);
                    field.SetValue(t, rowNum);
                    continue;
                }
                // 是否需要设置异常信息
                if (field.Name.ToLower() == ROW_TIPS.ToLower())
                {
                    hasRowTipsField = true;
                    continue;
                }
                // 原始数据
                if (field.Name.ToLower() == ROW_DATA.ToLower())
                {
                    field.SetValue(t, obj.ToString());
                    continue;
                }
                // 设置对应属性值
                SetFieldValue(t, field, obj, uniqueBuilder, errMsgList);
            }

            // 数据唯一性校验
            if (uniqueBuilder.Length > 0)
            {
                if (uniqueMap.ContainsValue(uniqueBuilder.ToString()))
                {
                    foreach (var pair in uniqueMap)
                    {
                        if (pair.Value == uniqueBuilder.ToString())
                        {
                            errMsgList.Add($"数据唯一性校验失败, ({uniqueBuilder}) 与第{pair.Key}行重复");
                        }
                    }
                }
                else
                {
                    uniqueMap.Add(rowNum, uniqueBuilder.ToString());
                }
            }

            // 失败处理
            if (errMsgList.Count == 0 && !hasRowTipsField)
            {
                return t;
            }

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            int size = errMsgList.Count;
            for (int i = 0; i < size; i++)
            {
                if (i == size - 1)
                {
                    sb.Append(errMsgList[i]);
                }
                else
                {
                    sb.Append(errMsgList[i]).Append(";");
                }
            }

            // 设置错误信息
            foreach (PropertyInfo field in fields)
            {
                if (field.Name.ToLower() == ROW_TIPS.ToLower())
                {
                    field.SetValue(t, sb.ToString());
                }
            }

            return t;
        }
        private static void SetFieldValue<T>(T instance, PropertyInfo field, JObject obj, StringBuilder uniqueBuilder, List<string> errMsgList)
        {
            // 获取 ExcelImport 注解属性
            ExcelImport annotation = (ExcelImport)Attribute.GetCustomAttribute(field, typeof(ExcelImport));
            if (annotation == null)
            {
                return;
            }
            string cname = annotation.Value;
            if (string.IsNullOrWhiteSpace(cname))
            {
                return;
            }
            // 获取具体值
            string val = null;
            if (obj.ContainsKey(cname))
            {
                val = GetString(obj.GetValue(cname).ToString());
            }
            if (val == null)
            {
                return;
            }
            // 判断是否必填
            bool require = annotation.Required;
            if (require && string.IsNullOrEmpty(val))
            {
                errMsgList.Add(string.Format("[{0}]不能为空", cname));
                return;
            }
            // 数据唯一性获取
            bool unique = annotation.Unique;
            if (unique)
            {
                if (uniqueBuilder.Length > 0)
                {
                    uniqueBuilder.Append("--").Append(val);
                }
                else
                {
                    uniqueBuilder.Append(val);
                }
            }
            // 判断是否超过最大长度
            int maxLength = annotation.MaxLength;
            if (maxLength > 0 && val.Length > maxLength)
            {
                errMsgList.Add(string.Format("[{0}]长度不能超过{1}个字符(当前{2}个字符)", cname, maxLength, val.Length));
            }
            // 判断当前属性是否有映射关系
            Dictionary<string, string> kvMap = GetKvMap(annotation.Kv);
            if (kvMap != null && kvMap.Count > 0)
            {
                bool isMatch = false;
                foreach (KeyValuePair<string, string> pair in kvMap)
                {
                    if (pair.Value.Equals(val))
                    {
                        val = pair.Key;
                        isMatch = true;
                        break;
                    }
                }
                if (!isMatch)
                {
                    errMsgList.Add(string.Format("[{0}]的值不正确(当前值为{1})", cname, val));
                    return;
                }
            }
            // 其余情况根据类型赋值
            string fieldClassName = field.PropertyType.Name;
            try
            {
                if (fieldClassName.Equals("String", StringComparison.OrdinalIgnoreCase))
                {
                    field.SetValue(instance, val);
                }
                else if (fieldClassName.Equals("Boolean", StringComparison.OrdinalIgnoreCase))
                {
                    field.SetValue(instance, bool.Parse(val));
                }
                else if (fieldClassName.Equals("Int32", StringComparison.OrdinalIgnoreCase))
                {
                    try
                    {
                        field.SetValue(instance, int.Parse(val));
                    }
                    catch (FormatException e)
                    {
                        errMsgList.Add(string.Format("[{0}]的值格式不正确(当前值为{1})", cname, val));
                    }
                }
                else if (fieldClassName.Equals("Double", StringComparison.OrdinalIgnoreCase))
                {
                    field.SetValue(instance, double.Parse(val));
                }
                else if (fieldClassName.Equals("Int64", StringComparison.OrdinalIgnoreCase))
                {
                    field.SetValue(instance, long.Parse(val));
                }
                else if (fieldClassName.Equals("Decimal", StringComparison.OrdinalIgnoreCase))
                {
                    field.SetValue(instance, decimal.Parse(val));
                }
                else if (fieldClassName.Equals("DateTime", StringComparison.OrdinalIgnoreCase))
                {
                    try
                    {
                        field.SetValue(instance, DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
                    }
                    catch (Exception)
                    {
                        field.SetValue(instance, DateTime.ParseExact(val, "yyyy-MM-dd", CultureInfo.InvariantCulture));
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] ExportTemplate<T>(FileInfo file, string sheetName)
        {
            return ExportTemplate<T>(file, sheetName, false);
        }
        public static byte[] ExportTemplate<T>(FileInfo file, string sheetName,
                                       bool isContainExample)
        {
            // 获取表头字段
            List<ExcelClassField> headFieldList = GetExcelClassFieldList<T>();
            // 获取表头数据和示例数据
            List<List<object>> sheetDataList = new List<List<object>>();
            List<object> headList = new List<object>();
            List<object> exampleList = new List<object>();
            Dictionary<int, List<string>> selectMap = new Dictionary<int, List<string>>();
            for (int i = 0; i < headFieldList.Count; i++)
            {
                ExcelClassField each = headFieldList[i];
                headList.Add(each.Name);
                exampleList.Add(each.Example);
                Dictionary<string, string> kvMap = each.KvMap;
                if (kvMap != null && kvMap.Count > 0)
                {
                    selectMap.Add(i, new List<string>(kvMap.Values));
                }
            }
            sheetDataList.Add(headList);
            if (isContainExample)
            {
                sheetDataList.Add(exampleList);
            }
            // 导出数据
            return Export(file, sheetName, sheetDataList, selectMap);
        }

        private static List<ExcelClassField> GetExcelClassFieldList<T>()
        {
            Type clazz = typeof(T);
            // 解析所有字段
            var fields = clazz.GetProperties();
            bool hasExportAnnotation = false;
            var map = new Dictionary<int, List<ExcelClassField>>();
            var sortList = new List<int>();
            foreach (var field in fields)
            {
                var cf = GetExcelClassField(field);
                if (cf.HasAnnotation == 1)
                {
                    hasExportAnnotation = true;
                }
                int sort = cf.Sort;
                if (map.ContainsKey(sort))
                {
                    map[sort].Add(cf);
                }
                else
                {
                    var list = new List<ExcelClassField>();
                    list.Add(cf);
                    sortList.Add(sort);
                    map.Add(sort, list);
                }
            }
            sortList.Sort();
            // 获取表头
            var headFieldList = new List<ExcelClassField>();
            if (hasExportAnnotation)
            {
                foreach (var sort in sortList)
                {
                    foreach (var cf in map[sort])
                    {
                        if (cf.HasAnnotation == 1)
                        {
                            headFieldList.Add(cf);
                        }
                    }
                }
            }
            else
            {
                headFieldList.AddRange(map[0]);
            }
            return headFieldList;
        }
        private static ExcelClassField GetExcelClassField(PropertyInfo field)
        {
            ExcelClassField cf = new ExcelClassField();
            string fieldName = field.Name;
            cf.FieldName = fieldName;
            ExcelExport annotation = field.GetCustomAttribute<ExcelExport>();
            // 无 ExcelExport 注解情况
            if (annotation == null)
            {
                cf.HasAnnotation = 0;
                cf.Name = fieldName;
                cf.Sort = 0;
                return cf;
            }
            // 有 ExcelExport 注解情况
            cf.HasAnnotation = 1;
            cf.Name = annotation.Value;
            string example = GetString(annotation.Example);
            if (!string.IsNullOrEmpty(example))
            {
                if (IsNumeric(example) && example.Length < 8)
                {
                    cf.Example = Convert.ToDouble(example);
                }
                else
                {
                    cf.Example = example;
                }
            }
            else
            {
                cf.Example = "";
            }
            cf.Sort = annotation.Sort;
            // 解析映射
            string kv = GetString(annotation.Kv);
            cf.KvMap = GetKvMap(kv);
            return cf;
        }
        private static Dictionary<string, string> GetKvMap(string kv)
        {
            Dictionary<string, string> kvMap = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(kv))
            {
                return kvMap;
            }
            string[] kvs = kv.Split(';');
            if (kvs.Length == 0)
            {
                return kvMap;
            }
            foreach (string each in kvs)
            {
                string[] eachKv = GetString(each).Split('-');
                if (eachKv.Length != 2)
                {
                    continue;
                }
                string k = eachKv[0];
                string v = eachKv[1];
                if (string.IsNullOrEmpty(k) || string.IsNullOrEmpty(v))
                {
                    continue;
                }
                kvMap[k] = v;
            }
            return kvMap;
        }
        private static String GetString(String s)
        {
            if (s == null)
            {
                return "";
            }
            if (s.Length == 0)
            {
                return s;
            }
            return s.Trim();
        }
        public static byte[] Export<T, K>(FileInfo file, string sheetName, List<T> sheetDataList)
        {
            bool listIsEmpty = sheetDataList == null || sheetDataList.Count == 0;
            //if (template == null || listIsEmpty)
            //{
            //    return ExportEmpty(file, sheetName);
            //}

            if (listIsEmpty)
            {
                return ExportTemplate<T>(file, sheetName);
            }
            List<List<object>> sheetDataListO = GetSheetData(sheetDataList);
            return Export(file, sheetName, sheetDataListO);

        }
        private static List<List<object>> GetSheetData<T>(List<T> list)
        {
            // 获取表头字段
            List<ExcelClassField> excelClassFieldList = GetExcelClassFieldList<T>();
            List<string> headFieldList = new List<string>();
            List<object> headList = new List<object>();
            Dictionary<string, ExcelClassField> headFieldMap = new Dictionary<string, ExcelClassField>();
            foreach (ExcelClassField each in excelClassFieldList)
            {
                string fieldName = each.FieldName;
                headFieldList.Add(fieldName);
                headFieldMap.Add(fieldName, each);
                headList.Add(each.Name);
            }
            // 添加表头名称
            List<List<object>> sheetDataList = new List<List<object>>();
            sheetDataList.Add(headList);
            // 获取表数据
            foreach (T t in list)
            {
                Dictionary<string, object> fieldDataMap = GetFieldDataMap(t);
                System.Collections.Generic.HashSet<string> fieldDataKeys = new System.Collections.Generic.HashSet<string>(fieldDataMap.Keys);
                List<object> rowList = new List<object>();
                foreach (string headField in headFieldList)
                {
                    if (!fieldDataKeys.Contains(headField))
                    {
                        continue;
                    }
                    object data = fieldDataMap[headField];
                    if (data == null)
                    {
                        rowList.Add("");
                        continue;
                    }
                    ExcelClassField cf = headFieldMap[headField];
                    // 判断是否有映射关系
                    Dictionary<string, string> kvMap = cf.KvMap;
                    if (kvMap == null || kvMap.Count == 0)
                    {
                        rowList.Add(data);
                        continue;
                    }
                    string val;
                    if (kvMap.TryGetValue(data.ToString(), out val))
                    {
                        if (IsNumeric(val))
                        {
                            rowList.Add(double.Parse(val));
                        }
                        else
                        {
                            rowList.Add(val);
                        }
                    }
                }
                sheetDataList.Add(rowList);
            }
            return sheetDataList;
        }
        private static Dictionary<string, object> GetFieldDataMap<T>(T t)
        {

            Dictionary<string, object> map = new Dictionary<string, object>();
            PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
            try
            {
                foreach (PropertyInfo property in properties)
                {
                    string propertyName = property.Name;
                    object propertyValue = property.GetValue(t);
                    map.Add(propertyName, propertyValue);
                }
            }
            catch (ArgumentException ex)
            {
                Console.WriteLine(ex.Message);
            }
            return map;
        }
        public static byte[] ExportEmpty(FileInfo file, string sheetName)
        {
            List<List<Object>> sheetDataList = new List<List<object>>();
            List<Object> headList = new List<object>();
            headList.Add("导出无数据");
            sheetDataList.Add(headList);
            return Export(file, sheetName, sheetDataList);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetDataList"></param>
        public static byte[] Export(FileInfo file, string sheetName, List<List<object>> sheetDataList)
        {
            return Export(file, sheetName, sheetDataList, null);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="file"></param>
        /// <param name="sheetName"></param>
        /// <param name="sheetDataList"></param>
        /// <param name="selectMap"></param>
        /// <returns></returns>
        public static byte[] Export(FileInfo file, string sheetName, List<List<object>> sheetDataList, Dictionary<int, List<string>> selectMap)
        {
            Dictionary<string, List<List<object>>> map = new Dictionary<string, List<List<object>>>();
            map.Add(sheetName, sheetDataList);
            return Export(file, map, selectMap);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="sheetName">表格名称</param>
        /// <param name="sheetDataList">表格数据</param>
        /// <param name="selectMap"></param>
        public static byte[] ExportManySheet(FileInfo file, Dictionary<string, List<List<object>>> sheetMap)
        {

            return Export(file, sheetMap, null);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="file">文件</param>
        /// <param name="sheetMap">工作博字典</param>
        /// <param name="selectMap">下拉框</param>
        /// <returns></returns>
        private static byte[] Export(FileInfo file, Dictionary<string, List<List<object>>> sheetMap, Dictionary<int, List<string>> selectMap)
        {
            //整个excel 表格book对象
            SXSSFWorkbook book = new SXSSFWorkbook();
            //每个sheet页
            // 将 Dictionary 的键值对转换为一个包含 KeyValuePair 的集合
            var entries = sheetMap.Select(kv => new KeyValuePair<string, List<List<object>>>(kv.Key, kv.Value)).ToList();
            foreach (var entry in entries)
            {
                List<List<object>> sheetDataList = entry.Value;
                ISheet sheet = book.CreateSheet(entry.Key);
                IDrawing patriarch = sheet.CreateDrawingPatriarch();

                //设置表头背景色(灰色)
                ICellStyle headStyle = book.CreateCellStyle();
                headStyle.FillForegroundColor = IndexedColors.Grey80Percent.Index;
                headStyle.FillPattern = FillPattern.SolidForeground;
                headStyle.Alignment = HorizontalAlignment.Center;
                headStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;

                //设置表身背景色(默认色)
                ICellStyle rowStyle = book.CreateCellStyle();
                rowStyle.Alignment = HorizontalAlignment.Center;
                rowStyle.VerticalAlignment = VerticalAlignment.Center;

                //设置表格列宽度(默认15个字节)
                sheet.DefaultColumnWidth = 15;
                //创建合并算法数组
                int rowLength = sheetDataList.Count;
                int columnLength = sheetDataList[0].Count;

                //二维数组,初始化
                int[,] mergeArray = new int[rowLength, columnLength];
                for (int i = 0; i < sheetDataList.Count; i++)
                {
                    //每个sheet页的行数据
                    IRow row = sheet.CreateRow(i);
                    List<object> rowList = sheetDataList[i];
                    for (int j = 0; j < rowList.Count; j++)
                    {
                        //每个行数据中的单元格数据
                        object o = rowList[j];
                        int v = 0;
                        if (o is Uri)
                        {
                            //如果要导出图片的话,链接需要传递uri对象
                            SetCellPicture(book, row, patriarch, i, j, (Uri)o);
                        }

                        else
                        {
                            ICell cell = row.CreateCell(j);
                            if (i == 0)
                            {
                                //第一行为表头行,采用灰色底背景
                                v = SetCellValue(cell, o, headStyle);
                            }
                            else
                            {
                                //其他行为数据行,默认白底色
                                v = SetCellValue(cell, o, rowStyle);

                            }
                        }
                        mergeArray[i, j] = v;
                    }
                }
                //合并单元格
                MergeCells(sheet, mergeArray);
                //设置下拉列表
                SetSelect(sheet, selectMap);


            }

            //写数据
            if (file == null)
            {
                //返回字节
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    //将工作博写入内存流
                    book.Write(memoryStream);
                    //将内存流的位置设置为0
                    //memoryStream.Seek(0, SeekOrigin.Begin);
                    // 返回内存流
                    // 此时您可以将内存流直接传递给需要的部分，如Web响应等
                    return memoryStream.ToArray();
                }

            }
            else
            {
                //本地导出
                // 将工作簿保存到文件
                using (FileStream fileStream = new FileStream(file.FullName, FileMode.Create, FileAccess.Write))
                {
                    book.Write(fileStream);
                  
                }
                // 读取写入的文件内容并转换为字节数组
                return File.ReadAllBytes(file.FullName);
            }
        }

        private static void SetSelect(ISheet sheet, Dictionary<int, List<string>> selectMap)
        {
            if (selectMap == null || selectMap.Count == 0)
            {
                return;
            }
            var entries = selectMap.Select(kv => new KeyValuePair<int, List<string>>(kv.Key, kv.Value)).ToList();
            foreach (var entry in entries)
            {
                int y = entry.Key;
                List<string> list = entry.Value;
                if (list == null || list.Count == 0)
                {
                    continue;
                }
                string[] arr = new string[list.Count];
                for (int i = 0; i < list.Count; i++)
                {
                    arr[i] = list[i];
                }
                IDataValidationHelper helper = sheet.GetDataValidationHelper();
                CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
                IDataValidationConstraint dvc = helper.CreateExplicitListConstraint(arr);
                IDataValidation dv = helper.CreateValidation(dvc, addressList);
                if (dv is HSSFDataValidation)
                {
                    dv.SuppressDropDownArrow = false;
                }
                else
                {
                    dv.SuppressDropDownArrow = true;
                    dv.ShowErrorBox = true;
                }
                sheet.AddValidationData(dv);
            }

        }
        /// <summary>
        /// 合并当前sheet页的单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mergeArray">合并单元格算法</param>
        /// <exception cref="NotImplementedException"></exception>
        private static void MergeCells(ISheet sheet, int[,] mergeArray)
        {
            // 横向合并
            for (int x = 0; x < mergeArray.GetLength(0); x++)
            {
                bool merge = false;
                int y1 = 0;
                int y2 = 0;
                for (int y = 0; y < mergeArray.GetLength(1); y++)
                {
                    var value = mergeArray[x, y];
                    if (value == CELL_COLUMN_MERGE)
                    {
                        if (!merge)
                        {
                            y1 = y;
                        }
                        y2 = y;
                        merge = true;

                    }
                    else
                    {
                        merge = false;
                        if (y1 > 0)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
                        }
                        y1 = 0;
                        y2 = 0;
                    }
                }
                if (y1 > 0)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
                }
            }
            // 纵向合并
            for (int y = 0; y < mergeArray.GetLength(1); y++)
            {
                bool merge = false;
                int x1 = 0;
                int x2 = 0;
                for (int x = 0; x < mergeArray.GetLength(0); x++)
                {
                    var value = mergeArray[x, y];
                    if (value == CELL_ROW_MERGE)
                    {
                        if (!merge)
                        {
                            x1 = x;
                        }
                        x2 = x;
                        merge = true;
                    }
                    else
                    {
                        merge = false;
                        if (x1 > 0)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                        }
                        x1 = 0;
                        x2 = 0;
                    }
                }
                if (x1 > 0)
                {
                    sheet.AddMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                }
            }





        }



        private static void SetCellPicture(SXSSFWorkbook book, IRow sr, IDrawing patriarch, int x, int y, Uri o)
        {
            // 设置图片宽高
            sr.Height = ((short)(IMG_WIDTH * IMG_HEIGHT));

            try
            {
                byte[] bytes = null;

                if (false == o.IsFile)
                {
                    bytes = HttpGet(o.ToString());
                }
                else
                {
                    bytes = File.ReadAllBytes(o.ToString());
                }
                var index = book.AddPicture(bytes, PictureType.JPEG);

                // 设置图片位置
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
                // 设置这个，图片会自动填满单元格的长宽
                anchor.AnchorType = (AnchorType.MoveAndResize);
                patriarch.CreatePicture(anchor, index);
            }
            catch (Exception e)
            {

            }

        }

        private static int SetCellValue(ICell cell, object o, ICellStyle style)
        {
            //设置央视
            cell.CellStyle = style;
            //数据为空时

            if (o == null)
            {
                cell.SetCellType(CellType.String);
                cell.SetCellValue("");
                return CELL_OTHER;
            }
            //是否为字符串
            if (o is string)
            {
                string s = o.ToString();
                //当数字类型长度超过8位时,改为字符串类型显示(excel数字超过一定长度会显示科学计数法)
                if (IsNumeric(s) && s.Length < 8)
                {
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToDouble(o));
                    return CELL_OTHER;
                }
                else
                {
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(s);
                }
                if (s == ROW_MERGE)
                {
                    return CELL_ROW_MERGE;
                }
                else if (s == COLUMN_MERGE)
                {
                    return CELL_COLUMN_MERGE;
                }
                else
                {
                    return CELL_OTHER;
                }

            }

            // 是否为字符串
            if (o is int || o is long || o is double || o is float)
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(Convert.ToDouble(o));
                return CELL_OTHER;

            }


            // 是否为Boolean
            if (o is bool)
            {
                cell.SetCellType(CellType.Boolean);
                cell.SetCellValue((Boolean)o);
                return CELL_OTHER;
            }
            // 如果是BigDecimal，则默认3位小数
            if (o is decimal)
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue((double)Math.Round(Convert.ToDecimal(o), 3));
                return CELL_OTHER;
            }
            // 如果是Date数据，则显示格式化数据
            if (o is DateTime)
            {
                cell.SetCellType(CellType.String);
                cell.SetCellValue(FormatDate(Convert.ToDateTime(o)));
                return CELL_OTHER;
            }
            // 如果是其他，则默认字符串类型
            cell.SetCellType(CellType.String);
            cell.SetCellValue(o.ToString());
            return CELL_OTHER;

        }

        private static string FormatDate(DateTime dateTime)
        {
            if (dateTime == null)
            {
                return "";
            }
            return dateTime.ToString(DATE_FORMAT);
        }

        private static bool IsNumeric(string str)
        {
            if (!string.IsNullOrEmpty(str) && "0.0" == str)
            {
                return true;
            }
            if (string.IsNullOrEmpty(str))
            {
                return false;
            }
            foreach (char item in str)
            {
                if (!char.IsDigit(item))
                {
                    return false;
                }
            }
            return true;
        }

        private static byte[] HttpGet(string url, string method = "GET")
        {
            try
            {
                // 创建 HttpWebRequest 对象
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = method;

                // 发送请求并获取响应
                using (WebResponse response = request.GetResponse())
                using (Stream stream = response.GetResponseStream())
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    // 将响应数据读取到内存流中
                    stream.CopyTo(memoryStream);
                    return memoryStream.ToArray();
                }
            }
            catch (Exception ex)
            {
                // 处理异常情况
                Console.WriteLine($"下载图片时发生异常: {ex.Message}");
                return null;
            }
        }
    }
}
